﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace WAF.DAO
{
    /// <summary>
    /// 通用数据库访问组件
    /// </summary>
    public sealed class DbAccess
    {
        //支持的数据库种类
        public enum DatabaseType { FIREBIRD, ODBC, OLEDB, ORACLE, MYSQL, MSSQL, SQLITE, SYBASE };

        //默认数据库类型和连接字符串(只读)。连接字符串格式"server=;database=;uid=;pwd="
        public static DatabaseType DefaultDbType     { get; private set; }
        public static string DefaultConnectionString { get; private set; }

        //当前实例的数据库类型(只读)。
        public DatabaseType CurrentDbType       { get; private set; }
        public string CurrentDbConnectionString { get; private set; }

        //是否允许调试
        private static bool isDebug;
        private static StreamWriter sw;

        private static Type DefaultDbConnectionType;    //默认数据库连接对象类型
        private static Type DefaultDbAdapterType;       //默认数据库适配器对象类型

        private IDbConnection   idbCon = null;
        private IDbCommand      idbCom = null;
        private IDbDataAdapter  idbAdapter = null;
        private IDbTransaction  idbTrans = null;


        /// <summary>
        /// 设定默认数据库种类和连接字符串
        /// 使用静态方法前须设置数据库类型和数据库连接字符串
        /// </summary>
        /// <param name="dbType">默认数据库种类</param>
        /// <param name="conString">默认连接字符串</param>
        /// <param name="debug">是否允许调试（输出SQL语句到调试文件）</param>
        /// <param name="logPath">调试文件路径</param>
        public static void SetDefaultDbType(DatabaseType dbType, string conString, bool debug = false, string path = "")
        {
            DefaultDbType = dbType;
            DefaultConnectionString = conString;

            isDebug = debug;
            if (isDebug) sw = new StreamWriter(path, true);

            GetDbType(dbType, out DefaultDbConnectionType, out DefaultDbAdapterType);
        }

        #region Init

        /// <summary>
        /// 默认构造函数，使用默认的数据库类型和连接字符串
        /// </summary>
        public DbAccess()
        {
            CreateInstance(DefaultConnectionString, DefaultDbConnectionType, DefaultDbAdapterType);
        }

        /// <summary>
        /// 构造函数，用指定数据库种类和连接字符串创建实例，用于访问默认数据库以外的数据库
        /// </summary>
        /// <param name="dbType">数据库种类</param>
        /// <param name="conString">连接字符串</param>
        public DbAccess(DatabaseType dbType, string conString)
        {
            CurrentDbType = dbType;
            CurrentDbConnectionString = conString;

            Type conType, adapterType;
            GetDbType(dbType, out conType, out adapterType);

            CreateInstance(conString, conType, adapterType);
        }

        /// <summary>
        /// 创建数据库组件实例
        /// </summary>
        /// <param name="conString">连接字符串</param>
        /// <param name="conType">连接对象类型</param>
        /// <param name="adapterType">适配器对象类型</param>
        private void CreateInstance(string conString, Type conType, Type adapterType)
        {
            this.idbCon = (IDbConnection)Activator.CreateInstance(conType);
            this.idbCon.ConnectionString = conString;

            this.idbCom = this.idbCon.CreateCommand();
            
            this.idbAdapter = (IDbDataAdapter)Activator.CreateInstance(adapterType);
            this.idbAdapter.SelectCommand = this.idbCom;
        }

        /// <summary>
        /// 根据指定的数据库种类获取该数据库的类型对象，以便动态创建实例
        /// </summary>
        /// <param name="dbType">数据库种类</param>
        /// <param name="connectionType">连接对象类型</param>
        /// <param name="adapterType">适配器对象类型</param>
        private static void GetDbType(DatabaseType dbType, out Type connectionType, out Type adapterType)
        {
            switch (dbType)
            {
                case DatabaseType.MSSQL:
                    connectionType  = typeof(SqlConnection);
                    adapterType     = typeof(SqlDataAdapter);
                    break;
                case DatabaseType.ODBC:
                    connectionType  = typeof(OdbcConnection);
                    adapterType     = typeof(OdbcDataAdapter);
                    break;
                case DatabaseType.OLEDB:
                    connectionType  = typeof(OleDbConnection);
                    adapterType     = typeof(OleDbDataAdapter);
                    break;
                case DatabaseType.FIREBIRD:
                    Assembly fbAssembly     = Assembly.LoadFrom(@"./FirebirdSql.Data.FirebirdClient.dll");
                    connectionType          = fbAssembly.GetType("FirebirdSql.Data.FirebirdClient.FbConnection");
                    adapterType             = fbAssembly.GetType("FirebirdSql.Data.FirebirdClient.FbDataAdapter");
                    break;
                case DatabaseType.ORACLE:
                    Assembly oracleAssembly = Assembly.LoadFrom(@"./Oracle.DataAccess.dll");
                    connectionType          = oracleAssembly.GetType("Oracle.DataAccess.Client.OracleConnection");
                    adapterType             = oracleAssembly.GetType("Oracle.DataAccess.Client.OracleDataAdapter");
                    break;
                case DatabaseType.MYSQL:
                    Assembly mysqlAssembly  = Assembly.LoadFrom(@"./mysql.data.dll");
                    connectionType          = mysqlAssembly.GetType("MySql.Data.MySqlClient.MySqlConnection");
                    adapterType             = mysqlAssembly.GetType("MySql.Data.MySqlClient.MySqlDataAdapter");
                    break;
                case DatabaseType.SQLITE:
                    Assembly sqliteAssembly = Assembly.LoadFrom(@"./System.Data.SQLite.dll");
                    connectionType          = sqliteAssembly.GetType("System.Data.SQLite.SQLiteConnection");
                    adapterType             = sqliteAssembly.GetType("System.Data.SQLite.SQLiteDataAdapter");
                    break;
                case DatabaseType.SYBASE:
                    Assembly sybaseAssembly = Assembly.LoadFrom(@"./Sybase.Data.AseClient.dll");
                    connectionType          = sybaseAssembly.GetType("Sybase.Data.AseClient.AseConnection");
                    adapterType             = sybaseAssembly.GetType("Sybase.Data.AseClient.AseDataAdapter");
                    break;
                default:
                    throw new Exception("未设置数据库类型或设置的数据库类型不在支持的范围内！");
            }
        }


        /// <summary>
        /// 初始化命令
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="parms">命名参数</param>
        private void initCmd(string sqlText, DbParameters parms = null)
        {
            this.idbCom.CommandType = CommandType.Text;
            this.idbCom.CommandText = sqlText;

            //准备参数
            if (parms != null)
            {
                foreach (DbParameters.ParmInfo parmInfo in parms.Items)
                {
                    IDataParameter parm = this.idbCom.CreateParameter();

                    parm.ParameterName = parmInfo.Key;
                    parm.DbType = parmInfo.DataType;
                    parm.Value = parmInfo.Value;

                    this.idbCom.Parameters.Add(parm);
                }
            }

            //输出要执行的SQL语句
            if (isDebug)
            {
                sw.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " " + sqlText);
                sw.Flush();
            }
        }

        #endregion

        #region Connection

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void Open()
        {
            if (this.idbCon.State != ConnectionState.Open)
                this.idbCon.Open();
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (this.idbCon.State != ConnectionState.Closed)
                this.idbCon.Close();
        }

        /// <summary>
        /// 读取或设置当前数据库
        /// </summary>
        /// <param name="dbName">要设置为当前数据库的名称，如要获取当前数据库名称则为空</param>
        /// <returns>当前数据库名称</returns>
        public string ChangeDatabase(string databaseName = "")
        {
            if (databaseName == "")
                databaseName = this.idbCon.Database;
            else
                this.idbCon.ChangeDatabase(databaseName);

            return databaseName;
        }

        /// <summary>
        /// 开始数据库事务
        /// </summary>
        public void BeginTransaction()
        {
            this.idbTrans = this.idbCon.BeginTransaction();
        }

        /// <summary>
        /// 提交数据库事务
        /// </summary>
        public void CommitTtransaction()
        {
            this.idbTrans.Commit();
        }

        /// <summary>
        /// 回滚数据库事务
        /// </summary>
        public void RollbackTtransaction()
        {
            this.idbTrans.Rollback();
        }

        #endregion


        /**************动态方法，支持事务**************/

        #region ExecuteNonQuery

        /// <summary>
        /// 执行一条SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parms">命名参数，参数名称必需以'@'开头</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string sqlText, DbParameters parms = null)
        {
            initCmd(sqlText, parms);

            return this.idbCom.ExecuteNonQuery();
        }

        #endregion

        #region ExecuteDataSet

        /// <summary>
        /// 执行一条SQL语句并返回结果集
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="parms">命名参数，参数名称必需以'@'开头</param>
        /// <returns>结果集</returns>
        public DataSet ExecuteDataSet(string sqlText, DbParameters parms = null)
        {
            initCmd(sqlText, parms);

            DataSet ds = new DataSet();
            this.idbAdapter.Fill(ds);

            return ds;
        }

        #endregion

        #region ExecuteReader

        /// <summary>
        /// 执行一条SQL语句并返回一个IDataReader
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="parms">命名参数，参数名称必需以'@'开头</param>
        /// <returns>DataReader</returns>
        public IDataReader ExecuteReader(string sqlText, DbParameters parms = null)
        {
            initCmd(sqlText, parms);

            return this.idbCom.ExecuteReader(CommandBehavior.CloseConnection);
        }

        #endregion

        #region ExecuteScalar

        /// <summary>
        /// 执行一条SQL语句并返回一个值
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="parms">命名参数，参数名称必需以'@'开头</param>
        /// <returns>返回值</returns>
        public object ExecuteScalar(string sqlText, DbParameters parms = null)
        {
            initCmd(sqlText, parms);

            return this.idbCom.ExecuteScalar();
        }

        #endregion


        /**************静态方法，不支持事务**************/

        #region ExeNonQuery

        /// <summary>
        /// 执行一条SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="parms">命名参数，参数名称必需以'@'开头</param>
        /// <returns>受影响的行数</returns>
        public static int ExeNonQuery(string sqlText, DbParameters parms = null)
        {
            int countRow = 0;
            DbAccess dbAccess = new DbAccess();

            dbAccess.Open();
            countRow = dbAccess.ExecuteNonQuery(sqlText, parms);
            dbAccess.Close();

            return countRow;
        }

        #endregion

        #region ExeDataSet

        /// <summary>
        /// 执行一条SQL语句并返回结果集
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="parms">命名参数，参数名称必需以'@'开头</param>
        /// <returns>结果集</returns>
        public static DataSet ExeDataSet(string sqlText, DbParameters parms = null)
        {
            DataSet ds = null;
            DbAccess dbAccess = new DbAccess();

            dbAccess.Open();
            ds = dbAccess.ExecuteDataSet(sqlText, parms);
            dbAccess.Close();

            return ds;
        }

        #endregion

        #region ExeReader

        /// <summary>
        /// 执行一条SQL语句并返回一个IDataReader
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="parms">命名参数，参数名称必需以'@'开头</param>
        /// <returns>DataReader</returns>
        public static IDataReader ExeReader(string sqlText, DbParameters parms = null)
        {
            DbAccess dbAccess = new DbAccess();

            dbAccess.Open();

            return dbAccess.ExecuteReader(sqlText, parms);
        }

        #endregion

        #region ExeScalar

        /// <summary>
        /// 执行一条SQL语句并返回一个值
        /// </summary>
        /// <param name="sqlText">SQL语句</param>
        /// <param name="parms">命名参数，参数名称必需以'@'开头</param>
        /// <returns>返回值</returns>
        public static object ExeScalar(string sqlText, DbParameters parms = null)
        {
            object obj = null;
            DbAccess dbAccess = new DbAccess();

            dbAccess.Open();
            obj = dbAccess.ExecuteScalar(sqlText, parms);
            dbAccess.Close();

            return obj;
        }

        #endregion
    }

    /****************参数类*****************/

    #region DbParameters

    /// <summary>
    /// 命名参数类
    /// </summary>
    public class DbParameters
    {
        private List<ParmInfo> parms = null;

        internal class ParmInfo
        {
            public string Key;
            public object Value;
            public DbType DataType;
        }

        public DbParameters()
        {
            parms = new List<ParmInfo>();
        }

        public DbParameters(string key, object value, DbType dbType)
        {
            parms = new List<ParmInfo>();
            this.Add(key, value, dbType);
        }

        /// <summary>
        /// 添加参数名称和值
        /// </summary>
        /// <param name="key">参数名称</param>
        /// <param name="value">参数值</param>
        public void Add(string key, object value, DbType dbType)
        {
            //参数占位符必需以'@’开头
            if (key.Substring(0, 1) != "@") key = "@" + key;

            ParmInfo parm = new ParmInfo();

            parm.Key = key;
            parm.Value = value;
            parm.DataType = dbType;

            parms.Add(parm);
        }

        /// <summary>
        /// 获取所有参数对象
        /// </summary>
        /// <returns>所有参数对象</returns>
        internal List<ParmInfo> Items
        {
            get { return parms; }
        }
    }

    #endregion
}
